package com.wanmait.shoppingsys.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.wanmait.shoppingsys.util.DbHelper;
import com.wanmait.shoppingsys.util.Pager;
import com.wanmait.shoppingsys.vo.Admin;
import com.wanmait.shoppingsys.vo.City;
import com.wanmait.shoppingsys.vo.Province;
import com.wanmait.shoppingsys.vo.Specialty;
import com.wanmait.shoppingsys.vo.Type;

public class CityDAO {
	  private static CityDAO cityDAO;
	  
	  private CityDAO() {}
	  
	  public static CityDAO getDAO() {
		  if(cityDAO==null) {
			  cityDAO=new CityDAO();
		  }
		  return cityDAO;
	  }
	  
	  public int getCityId(String city) {
		  int cityId = 0;
		  String sql = "select id from city where city.name like ?";
		  Connection conn = DbHelper.getConn();
		  PreparedStatement ps = null;
		ResultSet rs = null;
		
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1,"%"+city+"%");
			rs = ps.executeQuery();
			if(rs.next()) {
				cityId = rs.getInt("id");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			DbHelper.close(rs, ps, conn);
		}
		return cityId;
	
	  }
	  
	  public int getDataCount(Integer cityId) { //城市查找个数
			int count = 0;
			String sql = "select count(*) from specialty where specialty.city_id=?";
			Connection conn = DbHelper.getConn();
			PreparedStatement ps = null;
			ResultSet rs = null;
			
			try {
				ps = conn.prepareStatement(sql);
				ps.setInt(1,cityId);
				rs = ps.executeQuery();
				if(rs.next()) {
					count = rs.getInt(1);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			finally {
				DbHelper.close(rs, ps, conn);
			}
			return count;
		}
		
	  public Pager findByCity(Pager pager)     //搜索根据城市查找商品
		{
			ArrayList<Specialty> specialtys = new ArrayList<>();
			pager.setPageDate(specialtys);
			pager.setRowCount(this.getDataCount(pager.getCityId()));
			String sql="SELECT specialty.*,type.kind AS type_kind,city.name as city_name  from specialty LEFT JOIN type ON specialty.type_id=type.id left join city on city.id=specialty.city_id  WHERE specialty.city_id=? and specialty.enable = 1 LIMIT ?,?";
			Connection conn=DbHelper.getConn();
			PreparedStatement ps = null;
			ResultSet rs = null;
			try {
				ps = conn.prepareStatement(sql);
				ps.setInt(1,pager.getCityId());
				ps.setInt(2, (pager.getPageNum()-1)*pager.getPageSize());
				ps.setInt(3, pager.getPageSize());
				rs = ps.executeQuery();
				while(rs.next())
				{
					Specialty specialty = new Specialty();
					specialty.setId(rs.getInt("id"));
					specialty.setName(rs.getString("name"));			  		//商品名称
					specialty.setIntroduce(rs.getString("introduce"));			//商品介绍
					specialty.setPic(rs.getString("pic"));                		//商品图片
					specialty.setPrice(rs.getDouble("price"));					//商品价格
					
					//specialty.setInventory(rs.getInt("inventory"));	
					Type type=new Type();
					type.setId(rs.getInt("type_id"));
					type.setKind(rs.getString("type_kind"));
					specialty.setType(type);
					
					City city = new City();
					city.setId(rs.getInt("city_id"));
					city.setName(rs.getString("city_name"));      //城市名字
					specialty.setCity(city);
					
					specialtys.add(specialty);
					
				}
			} catch (Exception e) {
				e.printStackTrace();
			}finally {
				DbHelper.close(rs,ps,conn);
			}
			return pager;
			
		}
	  public Pager cityByPrice(Pager pager)     //搜索根据城市查找价格排序
		{
			ArrayList<Specialty> specialtys = new ArrayList<>();
			pager.setPageDate(specialtys);
			pager.setRowCount(this.getDataCount(pager.getCityId()));
			String sql="SELECT specialty.*,type.kind AS type_kind,city.name as city_name  from specialty LEFT JOIN type ON specialty.type_id=type.id left join city on city.id=specialty.city_id  WHERE specialty.city_id=? and specialty.enable = 1 order by specialty.price LIMIT ?,?";
			Connection conn=DbHelper.getConn();
			PreparedStatement ps = null;
			ResultSet rs = null;
			try {
				ps = conn.prepareStatement(sql);
				ps.setInt(1,pager.getCityId());
				ps.setInt(2, (pager.getPageNum()-1)*pager.getPageSize());
				ps.setInt(3, pager.getPageSize());
				rs = ps.executeQuery();
				while(rs.next())
				{
					Specialty specialty = new Specialty();
					specialty.setId(rs.getInt("id"));
					specialty.setName(rs.getString("name"));			  		//商品名称
					specialty.setIntroduce(rs.getString("introduce"));			//商品介绍
					specialty.setPic(rs.getString("pic"));                		//商品图片
					specialty.setPrice(rs.getDouble("price"));					//商品价格
					
					Type type=new Type();
					type.setId(rs.getInt("type_id"));
					type.setKind(rs.getString("type_kind"));
					specialty.setType(type);
					
					City city = new City();
					city.setId(rs.getInt("city_id"));
					city.setName(rs.getString("city_name"));      //城市名字
					specialty.setCity(city);
					
					specialtys.add(specialty);
					
				}
			} catch (Exception e) {
				e.printStackTrace();
			}finally {
				DbHelper.close(rs,ps,conn);
			}
			return pager;
			
		}
	  public Pager cityByLike(Pager pager)     //搜索根据城市查找商品
		{
			ArrayList<Specialty> specialtys = new ArrayList<>();
			pager.setPageDate(specialtys);
			pager.setRowCount(this.getDataCount(pager.getCityId()));
			String sql="SELECT specialty.*,type.kind AS type_kind,city.name as city_name  from specialty LEFT JOIN type ON specialty.type_id=type.id left join city on city.id=specialty.city_id  WHERE specialty.city_id=? and specialty.enable = 1 order by specialty.likecount desc LIMIT ?,?";
			Connection conn=DbHelper.getConn();
			PreparedStatement ps = null;
			ResultSet rs = null;
			try {
				ps = conn.prepareStatement(sql);
				ps.setInt(1,pager.getCityId());
				ps.setInt(2, (pager.getPageNum()-1)*pager.getPageSize());
				ps.setInt(3, pager.getPageSize());
				rs = ps.executeQuery();
				while(rs.next())
				{
					Specialty specialty = new Specialty();
					specialty.setId(rs.getInt("id"));
					specialty.setName(rs.getString("name"));			  		//商品名称
					specialty.setIntroduce(rs.getString("introduce"));			//商品介绍
					specialty.setPic(rs.getString("pic"));                		//商品图片
					specialty.setPrice(rs.getDouble("price"));					//商品价格
					
					//specialty.setInventory(rs.getInt("inventory"));	
					Type type=new Type();
					type.setId(rs.getInt("type_id"));
					type.setKind(rs.getString("type_kind"));
					specialty.setType(type);
					
					City city = new City();
					city.setId(rs.getInt("city_id"));
					city.setName(rs.getString("city_name"));      //城市名字
					specialty.setCity(city);
					
					specialtys.add(specialty);
					
				}
			} catch (Exception e) {
				e.printStackTrace();
			}finally {
				DbHelper.close(rs,ps,conn);
			}
			return pager;
			
		}
	  
	  
	  
	  public ArrayList<City> findAll(){//manage
		  ArrayList<City> citys = new ArrayList<City>();
		  
		  String sql="select * from city";
		  
		  Connection conn = DbHelper.getConn();
			PreparedStatement ps = null;
			ResultSet rs = null;
			try {
				ps = conn.prepareStatement(sql);
				rs = ps.executeQuery();
				while(rs.next()) {
					City city=new City();
					city.setId(rs.getInt("id"));
					city.setName(rs.getString("name"));
					citys.add(city);
				}
			}catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally {
				DbHelper.close(rs, ps, conn);
			}
			return citys;
	  }	
}
